﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class Pages_Query : DatabasePage
{

    int QueryId
    {
        get
        {
            if (Request.QueryString["QueryId"] != null)
            {
                return Convert.ToInt32(Request.QueryString["QueryId"]);
            }
            else
            {
                return 0;
            }
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (ConnectionId == 4)
            {
                txtQuery.Enabled = false;
                txtQuery.Text = "Queries not available on DEMO database";
                btnExecute.Enabled = false;
            }
            LoadTables();
            SetContentHeader();
            
            if (QueryId != 0)
            {
                DBCDS.QueryRow qRow = new DBCDSTableAdapters.QueryTableAdapter().GetDataById(QueryId).First();
                txtQueryName.Text = qRow.QueryName;
                txtQuery.Text = qRow.Query;
                ddlTable.SelectedValue = qRow.SelectTable;
                LoadColumns(ddlColumn, ddlTable.SelectedValue);
                ddlColumn.SelectedValue = qRow.SelectColumnId;

                Execute();
            }
        }
    }

    private void LoadTables()
    {
        //Table
        ddlTable.DataSource = DBData.DatabaseObjects(ConnectionString, DatabaseObjectTypes.Tables);
        ddlTable.DataTextField = "table_name";
        ddlTable.DataValueField = "table_name";
        ddlTable.DataBind();

        LoadColumns(ddlColumn, ddlTable.SelectedValue);

    }

    private void LoadColumns(DropDownList ddl, string tableName)
    {
        try
        {
            ddl.DataSource = GetColumns(tableName);
            ddl.DataTextField = "ColumnName";
            ddl.DataValueField = "ColumnName";
            ddl.DataBind();
        }
        catch (Exception ex)
        {
            //lblMessage.Text = ex.Message;
        }
    }

    public DataColumnCollection GetColumns(string tableName)
    {
        string selectStr = string.Format("SELECT TOP 1 * FROM [{0}] ", tableName);
        DataTable dt = DataAccess.GetDataTableSchema(ConnectionString, selectStr);
        return dt.Columns;
    }

    protected void btnExecute_Click(object sender, EventArgs e)
    {
        Execute();
    }

    private void Execute()
    {
        try
        {
            DateTime start = DateTime.Now;

            lblMessage.Text = string.Empty;

            if (txtQuery.Text.IndexOf("SELECT") > -1)
            {
                DataTable resultDT = DataAccess.GetDataTable(ConnectionString, txtQuery.Text);

                if (resultDT.Columns.Contains(ddlColumn.SelectedValue))
                {
                    gvQuery.DataKeyNames = new string[] { ddlColumn.SelectedValue };
                    gvQuery.Columns[0].Visible = true;
                }
                else
                {
                    gvQuery.Columns[0].Visible = false;
                }
                gvQuery.DataSource = resultDT;
                gvQuery.DataBind();

                lblMessage.Text = string.Format("{0} rows", resultDT.Rows.Count);
            }
            else
            {
                gvQuery.DataSource = null;
                gvQuery.DataBind();
                lblMessage.Text = DataAccess.ExecuteSql(ConnectionString, txtQuery.Text).ToString() + " rows affected";
            }

            lblMessage.Text += string.Format(" {0} mili sec.", (DateTime.Now - start).Milliseconds / 1);
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }

    protected void gvQuery_SelectedIndexChanged(object sender, EventArgs e)
    {
        string url = string.Format("ItemEdit.aspx?TableName={0}&IdColumn={1}&IdCt={2}&IdValue={3}", ddlTable.SelectedValue, gvQuery.DataKeyNames[0], "int", gvQuery.SelectedValue);
        Response.Redirect(url);

    }
    protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadColumns(ddlColumn, ddlTable.SelectedValue);
    }

    private void SetContentHeader()
    {
        OutlookMaster om = (OutlookMaster)Master;
        om.ContentHeader.Text = "Query";
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            lblMessage.Text = string.Empty;
            if (QueryId == 0)
            {
                new DBCDSTableAdapters.QueryTableAdapter().Insert(
                    txtQueryName.Text,
                    ConnectionId,
                    txtQuery.Text,
                    ddlTable.SelectedValue,
                    ddlColumn.SelectedValue);
            }
            else
            {
                new DBCDSTableAdapters.QueryTableAdapter().Update(
                    txtQueryName.Text,
                    ConnectionId,
                    txtQuery.Text,
                    ddlTable.SelectedValue,
                    ddlColumn.SelectedValue,
                    QueryId);
            }
            lblMessage.Text = "Saved " + DateTime.Now.ToString();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            lblMessage.Text = string.Empty;
            new DBCDSTableAdapters.QueryTableAdapter().Delete(QueryId);
            lblMessage.Text = "Deleted " + DateTime.Now.ToString();
            txtQuery.Text = string.Empty;
            txtQueryName.Text = string.Empty;
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }
}